﻿using System;
using System.Data.SqlClient;
using System.Data;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class OrganizationDao
    {
        private readonly SqlConnection _sqlConnection;

        /// <summary>
        /// Initializes a new instance of the <see cref="OrganizationDao"/> class.
        /// </summary>
        public OrganizationDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        #region Query string sql
        private const string QueryStringSelectAllOrganization = @"
                                SELECT [Name], Address1, PostCode, 
                                FirstName + ' ' + SurName as ContactName, 
                                O.ID, O.Name, O.Address1, 
                                O.PostCode, 
                                C.FirstName + ' ' + C.SurName as FullName, 
	                            CASE(O.IsActive) WHEN 'True' THEN 'Yes' ELSE 'No' END AS IsActive
	                            FROM Organizations O, Contact C
	                            WHERE O.ContactID = C.ContactID";
        private const string SqlGetOrganizationById = @"
                                SELECT * FROM Organizations WHERE Id = @OrganizationId";
        private const string OrganizationInsert = @"INSERT INTO [Organizations]
           ([Name],[ShortDescription],[ContactID],[Address1],[Address2],[Address3],[City],[County],[Country],[PreferredOrg]
           ,[ExpOfInterest],[TypeOfBusinessId],[FullDescription],[PhoneNumber],[Fax],[Email],[WebAddr],[CharityNumber]
           ,[CompanyNumber],[GovOffRegionID],[TrustRegionID],[TrustDistrictID],[PostCode],[IsActive])
            VALUES
           (@Name,@ShortDescription,@ContactID,@Address1,@Address2,@Address3,@City,@County,@Country,@PreferredOrg,@ExpOfInterest, 
           @TypeOfBusinessId,@FullDescription,@PhoneNumber,@Fax,@Email,@WebAddr,@CharityNumber,@CompanyNumber,@GovOffRegionID, 
           @TrustRegionID,@TrustDistrictID,@PostCode,@IsActive)";
        private const string OrganizationUpdate = @"UPDATE [Organizations]
                                                   SET [Name] = @Name, 
                                                      [ShortDescription] = @ShortDescription,
                                                      [ContactID] = @ContactID
                                                      ,[Address1] = @Address1
                                                      ,[Address2] = @Address2
                                                      ,[Address3] = @Address3
                                                      ,[City] = @City
                                                      ,[County] = @County
                                                      ,[Country] = @Country
                                                      ,[PreferredOrg] = @PreferredOrg
                                                      ,[ExpOfInterest] = @ExpOfInterest
                                                      ,[TypeOfBusinessId] = @TypeOfBusinessId
                                                      ,[FullDescription] = @FullDescription
                                                      ,[PhoneNumber] = @PhoneNumber
                                                      ,[Fax] = @Fax
                                                      ,[Email] = @Email
                                                      ,[WebAddr] = @WebAddr
                                                      ,[CharityNumber] = @CharityNumber
                                                      ,[CompanyNumber] = @CompanyNumber                                                      
                                                      ,[GovOffRegionID] = @GovOffRegionID
                                                      ,[TrustRegionID] = @TrustRegionID
                                                      ,[TrustDistrictID] = @TrustDistrictID
                                                      ,[PostCode] = @PostCode
                                                      ,[IsActive] = @IsActive
                                                 WHERE ID = @ID";
        private const string SqlCheckOrganizationNameExists =
           @"SELECT COUNT(Name) FROM Organizations WHERE Name = @Name";
        private const string SqlMarkActivated =
           @"UPDATE Organizations SET IsActive = @IsActive WHERE ID = @ID";
        private const string SqlGetOrganizationId =
           @"SELECT ID FROM Organizations WHERE Name = @Name";

        #endregion

        #region method
        /// <summary>
        /// Selects all Organization.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectAllOrganization()
        {
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectAllOrganization);
            return ds.Tables[0];
        }
        /// <summary>
        /// Selects the Organization id.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public DataTable SelectOrganizationId(string name)
        {
            var param = new SqlParameter[1];
            param[0] = new SqlParameter("@Name", SqlDbType.NVarChar) {Value = name};
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlGetOrganizationId, param);
            return ds.Tables[0];
        }
        /// <summary>
        /// Returns the Organization by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public Organization ReturnOrganizationById(int id)
        {
            var parameter = new SqlParameter("@OrganizationId", SqlDbType.Int) { Value = id };
            var dataRow =
                SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlGetOrganizationById, parameter).Tables[0].
                    Rows[0];
            return new Organization
                {
                    Id = (int)dataRow["ID"],
                    Name = (string)dataRow["Name"],
                    ShortDescription = (string)dataRow["ShortDescription"],
                    ContactId = (int)dataRow["ContactID"],
                    Address1 = (string)dataRow["Address1"],
                    Address2 = (string)dataRow["Address2"],
                    Address3 = (string)dataRow["Address3"],
                    City = (string)dataRow["City"],
                    County = (string)dataRow["County"],
                    Country = (string)dataRow["Country"],
                    PreferredOrg = (bool)dataRow["PreferredOrg"],
                    ExpOfInterest = (bool)dataRow["ExpOfInterest"],
                    TypeOfBusinessId = (int)dataRow["TypeOfBusinessId"],
                    FullDescription = (string)dataRow["FullDescription"],
                    PhoneNumber = (string)dataRow["PhoneNumber"],
                    Fax = (string)dataRow["Fax"],
                    Email = (string)dataRow["Email"],
                    WebAddress = (string)dataRow["WebAddr"],
                    CharityNumber = (string)dataRow["CharityNumber"],
                    CompanyNumber = (string)dataRow["CompanyNumber"],
                    GovOffRegionId = (int)dataRow["GovOffRegionId"],
                    TrustRegionId = (int)dataRow["TrustRegionId"],
                    TrustDistrictId = (int)dataRow["TrustDistrictId"],
                    Postcode = (string)dataRow["PostCode"],
                    IsActive = (bool)dataRow["IsActive"]
                };
        }
        /// <summary>
        /// Inserts the specified Organizations.
        /// </summary>
        /// <param name="organizations">The Organizations.</param>
        /// <returns></returns>
        public bool Insert(Organization organizations)
        {
            try
            {
                var parameters = new SqlParameter[24];
                parameters[0] = new SqlParameter("@Name", SqlDbType.VarChar) { Value = organizations.Name };
                parameters[1] = new SqlParameter("@ShortDescription", SqlDbType.NText) { Value = organizations.ShortDescription };
                parameters[2] = new SqlParameter("@ContactID", SqlDbType.Int) { Value = organizations.ContactId };
                parameters[3] = new SqlParameter("@Address1", SqlDbType.NVarChar) { Value = organizations.Address1 };
                parameters[4] = new SqlParameter("@Address2", SqlDbType.NVarChar) { Value = organizations.Address2 };
                parameters[5] = new SqlParameter("@Address3", SqlDbType.NVarChar) { Value = organizations.Address3 };
                parameters[6] = new SqlParameter("@City", SqlDbType.NVarChar) { Value = organizations.City };
                parameters[7] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = organizations.County };
                parameters[8] = new SqlParameter("@Country", SqlDbType.NVarChar) { Value = organizations.Country };
                parameters[9] = new SqlParameter("@PreferredOrg", SqlDbType.Bit) { Value = organizations.PreferredOrg };
                parameters[10] = new SqlParameter("@ExpOfInterest", SqlDbType.Bit) { Value = organizations.ExpOfInterest };
                parameters[11] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = organizations.TypeOfBusinessId };
                parameters[12] = new SqlParameter("@FullDescription", SqlDbType.NText) { Value = organizations.FullDescription };
                parameters[13] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar) { Value = organizations.PhoneNumber };
                parameters[14] = new SqlParameter("@Fax", SqlDbType.NVarChar) { Value = organizations.Fax };
                parameters[15] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = organizations.Email };
                parameters[16] = new SqlParameter("@WebAddr", SqlDbType.NVarChar) { Value = organizations.WebAddress };
                parameters[17] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar) { Value = organizations.CharityNumber };
                parameters[18] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar) { Value = organizations.CompanyNumber };
                parameters[19] = new SqlParameter("@GovOffRegionID", SqlDbType.Int) { Value = organizations.GovOffRegionId };
                parameters[20] = new SqlParameter("@TrustRegionID", SqlDbType.Int) { Value = organizations.TrustRegionId };
                parameters[21] = new SqlParameter("@TrustDistrictID", SqlDbType.Int) { Value = organizations.TrustDistrictId };
                parameters[22] = new SqlParameter("@PostCode", SqlDbType.NVarChar) { Value = organizations.Postcode };
                parameters[23] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = organizations.IsActive };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, OrganizationInsert, parameters) != 0;
            }
            catch (SqlException exception)
            {

                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }

        }
        /// <summary>
        /// Edits the specified Organizations.
        /// </summary>
        /// <param name="organizations">The Organizations.</param>
        /// <returns></returns>
        public bool Edit(Organization organizations)
        {
            try
            {
                var parameters = new SqlParameter[25];
                parameters[0] = new SqlParameter("@Name", SqlDbType.VarChar) { Value = organizations.Name };
                parameters[1] = new SqlParameter("@ShortDescription", SqlDbType.NText) { Value = organizations.ShortDescription };
                parameters[2] = new SqlParameter("@ContactID", SqlDbType.Int) { Value = organizations.ContactId };
                parameters[3] = new SqlParameter("@Address1", SqlDbType.NVarChar) { Value = organizations.Address1 };
                parameters[4] = new SqlParameter("@Address2", SqlDbType.NVarChar) { Value = organizations.Address2 };
                parameters[5] = new SqlParameter("@Address3", SqlDbType.NVarChar) { Value = organizations.Address3 };
                parameters[6] = new SqlParameter("@City", SqlDbType.NVarChar) { Value = organizations.City };
                parameters[7] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = organizations.County };
                parameters[8] = new SqlParameter("@Country", SqlDbType.NVarChar) { Value = organizations.Country };
                parameters[9] = new SqlParameter("@PreferredOrg", SqlDbType.Bit) { Value = organizations.PreferredOrg };
                parameters[10] = new SqlParameter("@ExpOfInterest", SqlDbType.Bit) { Value = organizations.ExpOfInterest };
                parameters[11] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = organizations.TypeOfBusinessId };
                parameters[12] = new SqlParameter("@FullDescription", SqlDbType.NText) { Value = organizations.FullDescription };
                parameters[13] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar) { Value = organizations.PhoneNumber };
                parameters[14] = new SqlParameter("@Fax", SqlDbType.NVarChar) { Value = organizations.Fax };
                parameters[15] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = organizations.Email };
                parameters[16] = new SqlParameter("@WebAddr", SqlDbType.NVarChar) { Value = organizations.WebAddress };
                parameters[17] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar) { Value = organizations.CharityNumber };
                parameters[18] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar) { Value = organizations.CompanyNumber };
                parameters[19] = new SqlParameter("@GovOffRegionID", SqlDbType.Int) { Value = organizations.GovOffRegionId };
                parameters[20] = new SqlParameter("@TrustRegionID", SqlDbType.Int) { Value = organizations.TrustRegionId };
                parameters[21] = new SqlParameter("@TrustDistrictID", SqlDbType.Int) { Value = organizations.TrustDistrictId };
                parameters[22] = new SqlParameter("@PostCode", SqlDbType.NVarChar) { Value = organizations.Postcode };
                parameters[23] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = organizations.IsActive };
                parameters[24] = new SqlParameter("@ID", SqlDbType.Bit) { Value = organizations.Id };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, OrganizationUpdate, parameters) != 0;
            }
            catch (SqlException exception)
            {

                ExceptionHandler.WriteErrorLog(exception);
                throw;
            }
        }
        /// <summary>
        /// Checks the Organization name exists.
        /// </summary>
        /// <param name="organizationName">Name of the Organization.</param>
        /// <returns></returns>
        public bool CheckOrganizationNameExists(string organizationName)
        {
            try
            {
                var sqlParam = new SqlParameter[1];
                sqlParam[0] = new SqlParameter("@Name", SqlDbType.VarChar) { Value = organizationName };
                return (int)SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, SqlCheckOrganizationNameExists, sqlParam) != 0;
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }
        /// <summary>
        /// Marks the activated.
        /// </summary>
        /// <param name="isActive">if set to <c>true</c> [is active].</param>
        /// <param name="organizationId">The Organization id.</param>
        /// <returns></returns>
        public bool MarkActivated(bool isActive, int organizationId)
        {
            try
            {
                var sqlParam = new SqlParameter[2];
                sqlParam[0] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = isActive };
                sqlParam[1] = new SqlParameter("@ID", SqlDbType.Int) { Value = organizationId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlMarkActivated, sqlParam) != 0;
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }
        /// <summary>
        /// Lists the organization role.
        /// </summary>
        /// <returns></returns>
        public DataTable ListOrganizationRole(int serviceId)
        {
            try
            {
                var parameter = new SqlParameter("@ServiceId", SqlDbType.Int) {Value = serviceId};
                var dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                         DBSchemas.ListOrganizationRole, parameter);
                var dataTable = dataSet.Tables[0];
                return dataTable;
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return null;
            }
        }
        /// <summary>
        /// Updates the organization role.
        /// </summary>
        /// <param name="organizationId">The organization id.</param>
        /// <param name="roleId">The role id.</param>
        /// <returns></returns>
        public int UpdateOrganizationRole(int organizationId, int roleId)
        {
            try
            {
                var parameter = new SqlParameter[2];
                parameter[0] = new SqlParameter("@OrganizationId", SqlDbType.Int) { Value = organizationId };
                parameter[1] = new SqlParameter("@RoleId", SqlDbType.Int) { Value = roleId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text,
                                                        DBSchemas.UpdateOrganizationRole, parameter);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return 0;
            }
            catch(ArgumentException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }
        /// <summary>
        /// Deletes the organization role.
        /// </summary>
        /// <param name="organizationId">The organization id.</param>
        /// <returns></returns>
        public int DeleteOrganizationRole(int organizationId)
        {
            try
            {
                var parameter = new SqlParameter("@OrganizationId", SqlDbType.Int) {Value = organizationId};
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.DeleteAllOrganizationRole,
                                                 parameter);

            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return 0;
            }
            catch(ArgumentException argumentException)
            {
                ExceptionHandler.WriteErrorLog(argumentException);
                return 0;
            }
        }
        public Organization RetrieveOrganizationIdByDepartment(int departmentId)
        {
            var parameter = new SqlParameter("@DepartmentId", SqlDbType.Int) { Value = departmentId };
            var dataRow =
                SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.SqlRetrieveOrganizationIdByDepartment, parameter).Tables[0].
                    Rows[0];
            return new Organization
            {
                Id = (int)dataRow["OrganizationId"],
                Name = (string)dataRow["Name"],
                ShortDescription = (string)dataRow["ShortDescription"],
                ContactId = (int)dataRow["ContactID"],
                Address1 = (string)dataRow["Address1"],
                Address2 = (string)dataRow["Address2"],
                Address3 = (string)dataRow["Address3"],
                City = (string)dataRow["City"],
                County = (string)dataRow["County"],
                Country = (string)dataRow["Country"],
                PreferredOrg = (bool)dataRow["PreferredOrg"],
                ExpOfInterest = (bool)dataRow["ExpOfInterest"],
                TypeOfBusinessId = (int)dataRow["TypeOfBusinessId"],
                FullDescription = (string)dataRow["FullDescription"],
                PhoneNumber = (string)dataRow["PhoneNumber"],
                Fax = (string)dataRow["Fax"],
                Email = (string)dataRow["Email"],
                WebAddress = (string)dataRow["WebAddr"],
                CharityNumber = (string)dataRow["CharityNumber"],
                CompanyNumber = (string)dataRow["CompanyNumber"],
                GovOffRegionId = (int)dataRow["GovOffRegionId"],
                TrustRegionId = (int)dataRow["TrustRegionId"],
                TrustDistrictId = (int)dataRow["TrustDistrictId"],
                Postcode = (string)dataRow["PostCode"],
                IsActive = (bool)dataRow["IsActive"]
            };
        }
        #endregion
    }
}
